package com.letv.download;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.letv.common.ConnectionManager;
import com.letv.util.SQLUtil;

/**
 * common method for download data from cms
 */
public class DownloadUtils {

    /**
     * Logger
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(DownloadUtils.class);

    /**
     * String, cp info table name in database
     */
    private static final String CP_INFO_TABLE_NAME = "cp_info";

    /**
     * copy table from table to oldTable.
     * if exist oldTable,drop oldTable first.
     * if have any errors while running sql,rollback
     * @param connection Connection, connection from data base.this connection will be connection from local as usual
     * @param tableName String
     * @param oldTableName String
     * @return boolean, success--true; fail--false
     */
    public synchronized boolean copyTable(Connection connection, String tableName, String oldTableName) {
        String renameSql = "rename table " + tableName + " to " + oldTableName;
        String createSql = "create table " + tableName + " select * from " + oldTableName
                + " where 1<>1";
        String addPrikeySql = "";
        if("index_leso".equals(tableName) || "leso".equals(tableName)) {
            addPrikeySql = "alter table " + tableName + " add primary key(ID,SOURCE)";
        } else {
            addPrikeySql = "alter table " + tableName + " add primary key(ID)";
        }
        String dynamicFormat = "alter table " + tableName + " ROW_FORMAT=DYNAMIC";
        String dropSql = "DROP TABLE IF EXISTS " + oldTableName;
        String addRealCreateTime = "";
        String addIndexVid = "";
        if("index_leso".equals(tableName)) {
            addRealCreateTime = "ALTER TABLE " + tableName + " CHANGE realCreateTime realCreateTime TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP";
            addIndexVid = "ALTER TABLE " + tableName + " ADD INDEX index_vid (vid)";
        }

        try {
            connection.setAutoCommit(false);
            Statement stmt = connection.createStatement();

            stmt.executeUpdate(dropSql);
            LOGGER.info("[" + dropSql + "]");
            stmt.executeUpdate(renameSql);
            LOGGER.info("[" + renameSql + "]");
            stmt.executeUpdate(createSql);
            LOGGER.info("[" + createSql + "]");
            stmt.executeUpdate(addPrikeySql);
            LOGGER.info("[" + addPrikeySql + "]");
            stmt.executeUpdate(dynamicFormat);
            LOGGER.info("[" + dynamicFormat + "]");
            if("index_leso".equals(tableName)) {
                stmt.executeUpdate(addRealCreateTime);
                LOGGER.info("[" + addRealCreateTime + "]");
                stmt.executeUpdate(addIndexVid);
                LOGGER.info("[" + addIndexVid + "]");
            }
            connection.commit();
            LOGGER.info("table copy done!");
            return true;
        } catch (SQLException e2) {
            LOGGER.error("table copy error!", e2);
            try {
                connection.rollback();
                LOGGER.info("table copy error! rollback!");
            } catch (SQLException e) {
                e.printStackTrace();
                LOGGER.error("rollback error!");
            }
            return false;
        }
    }

    /**
     * get the range of cp_info id from database
     * If there is no mistake,the size of the result list is 2.index 0 is the min id,index 1 is the max id
     * @param summary
     * @return List<Integer>, return null when have errors
     */
    public List<Integer> getIdRangeList() {
        ConnectionManager connectionManager = null;
        try {
            connectionManager = ConnectionManager.getInstance();
        } catch (Exception e) {
            LOGGER.error("cp_info fail to get id range,connection get instance error!", e);
            return null;
        }

        Connection connectionFromLeso = null;
        try {
            connectionFromLeso = connectionManager.getConnectionFromLeso();
        } catch (Exception e) {
            LOGGER.error("cp_info fail to get id range,connection get connectionFromLeso error!", e);
            return null;
        }

        List<String> idRange = null;
        try {
            idRange = SQLUtil.getIdRangeFromTableName(connectionFromLeso, DownloadUtils.CP_INFO_TABLE_NAME);
        } catch(Exception e) {
            LOGGER.error("cp_info fail to get id range,sql method call error!", e);
            return null;
        } finally{
            if(!SQLUtil.close(connectionFromLeso)) {
                LOGGER.error("fail to close connectionFromLeso when get cp_info id range");
            }
        }
        if(null == idRange) {
            LOGGER.error("cp_info fail to get id range,the sql return is null");
            return null;
        }
        if(idRange.size() != 2) {
            LOGGER.error("cp_info fail to get id range,the list size is " + idRange.size() + ",not 2");
            return null;
        }
        List<Integer> result = null;
        try {
            result = new ArrayList<Integer>();
            result.add(Integer.parseInt(idRange.get(0)));
            result.add(Integer.parseInt(idRange.get(1)));
        } catch(Exception e) {
            LOGGER.error("cp_info fail to get id range,fail to convert from String to Integer");
            return null;
        }
        return result;
    }
}
